# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, Date, Float, Boolean,DateTime,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
import os_package
from datetime import datetime
Base = declarative_base()

class Jinniu_Teacher_Info(Base):
    __tablename__ = 'jinniu_teacher_info'
    id:int = Column(Integer, primary_key=True, autoincrement=True)
    # 教师姓名
    teacher_name:str = Column(String(255))
    # 教师账号
    teacher_account:str = Column(String(255),unique=True)
    # 教师密码
    teacher_password:str = Column(String(255))
    # 教师账号状态 == 是否停用
    teacher_active:bool = Column(Boolean,insert_default=False)
    # 教师账号当前登陆状态
    teacher_login_status:bool = Column(Boolean,insert_default=False)
    # 注册时间
    registration_time:datetime = Column(DateTime,insert_default=datetime.now())
    # 上一次登陆时间
    last_login_time:datetime = Column(DateTime,insert_default=datetime.now())
    # 备注
    remarks:str = Column(String(255))

class Jinniu_School_Info(Base):
    __tablename__ = 'jinniu_school_info'
    id:int = Column(Integer, primary_key=True, autoincrement=True)
    # 省份
    province:str = Column(String(255))
    # 市区
    urban_area:str = Column(String(255))
    # 区县
    district_and_county:str = Column(String(255))
    # 学校名字
    school_name:str = Column(String(255),unique=True)
    # 学校地址
    school_address:str = Column(String(255))
    # 学校状态 == 是否停止合作
    school_active:bool = Column(Boolean,insert_default=False)
    # 合作年月
    cooperation_period:str = Column(String(255))
    # 支教负责人
    person_teacher:str = Column(String(255),ForeignKey('jinniu_teacher_info.teacher_name'))
    # 备注
    remarks:str = Column(String(255))

class Jinniu_Student_Info(Base):
    __tablename__ = 'jinniu_student_info'
    id:int = Column(Integer, primary_key=True, autoincrement=True)
    # 学生姓名
    student_name:str = Column(String(255))
    # 班级名
    class_name:str = Column(String(255))
    # 学生隶属学校
    student_school:str = Column(String(255),ForeignKey('jinniu_school_info.school_name'))
    # 学生年级
    student_grade:str = Column(String(255))
    # 学生班级
    student_class:int = Column(Integer)
    # 学生类型 == 普教班、竞赛班
    student_type:str = Column(String(255))
    # 学生状态 == 是否退课
    student_status:bool = Column(Boolean,insert_default=False)
    # 授课教师
    instructor_teacher:str  = Column(String(255),ForeignKey('jinniu_teacher_info.teacher_name'))
    # 备注
    remarks:str = Column(String(255))


class sql_main():
    def __init__(self,mode=0):
        """
        mode
        0=sqlite
        1=mysql
        关键词：root,password,port,host,
        其他
        """
        self.mode = mode
        self.engine = None
        self.session = None
        self.os_opt = os_package.Os_Option()
        self.os_opt.make_file_folder('database')
        self.Session = None
        self.is_echo = False

    def __del__(self):
        if self.session is not None:
            self.session.close_all()
        if self.engine is not None:
            self.engine.dispose()

    def login_mode(self,**kwargs):
        try:
            # 连接sqlite
            if self.mode == 0:
                self.engine = create_engine(f'sqlite:///database/{self.os_opt.get_current_file_name()[0]}.db',echo=self.is_echo)
            # 连接mysql
            elif self.mode == 1:
                self.engine = create_engine(f'mysql+pymysql://{kwargs["root"]}:{kwargs["password"]}@{kwargs["localhost"]}:{kwargs["port"]}',echo=self.is_echo)
            # 连接其他的
            else:
                raise Exception('暂未配置')
        except Exception as e:
            print(e)
            return False
        # 创建所有表
        Base.metadata.create_all(self.engine)
        # 创建会话工厂
        self.Session = sessionmaker(bind=self.engine)
        # 创建会话实例
        # self.session = self.Session()
        return True

    def insert_data(self,table_name:str,insert_data_list_dict:list):
        """
        添加数据到数据库

        参数:
        table_name (str): 表名
        insert_data_list_dict (list): 要插入的数据列表

        返回:
        bool: 成功添加数据返回True，否则返回False

        示例：
        table_name = 'Jinniu_Teacher_Info'
        insert_data_list_dict = [
        {'teacher_name': '张三', 'teacher_account': 'zhangsan', 'teacher_password': '123456', 'teacher_active': True,
        'teacher_login_status': False, 'registration_time': datetime.now(), 'last_login_time': datetime.now(),
        'remarks': '无'},
        {'teacher_name': '李四', 'teacher_account': 'lisi', 'teacher_password': '654321', 'teacher_active': True,
        'teacher_login_status': False, 'registration_time': datetime.now(), 'last_login_time': datetime.now(),
        'remarks': '无'}
        ]
        """
        try:
            self.session = self.Session()
            # 获取表对应的类
            table_class = globals().get(self.convert_to_camel_case_with_underscores(table_name))
            if not table_class or not issubclass(table_class, Base):
                raise Exception(f"警告: 未找到表类 {table_name}")
            try:
                with self.session.begin():
                    self.session.bulk_insert_mappings(table_class,insert_data_list_dict)
            except Exception as e:
                raise Exception(e)

            self.session.close()
            return True
        except Exception as e:
            print(f"数据添加过程中发生错误: {str(e)}")
            self.session.rollback()
            self.session.close()
            return False

    def query_data(self, table_name: str, query_conditions: dict = None,select_columns: list = None):
        """
        查询数据库中的数据

        参数:
        table_name (str): 表名
        query_conditions (dict): 查询条件，例如 {'teacher_name': '张三'}
        select_columns (list): 需要选择的列，例如 ['teacher_name', 'teacher_account']

        返回:
        list: 查询结果列表

        示例：
        table_name = 'Jinniu_Teacher_Info'
        query_conditions = {'teacher_name': '张三'}
        select_columns = ['teacher_name']
        """
        try:
            self.session = self.Session()
            # 获取表对应的类
            table_class = globals().get(self.convert_to_camel_case_with_underscores(table_name))
            if not table_class or not issubclass(table_class, Base):
                raise Exception(f"警告: 未找到表类 {table_name}")
            try:
                # 如果没有查询条件，则查询所有数据
                if query_conditions is None:
                    if select_columns is None:
                        result = self.session.query(table_class).all()
                        self.session.close()
                        return [{key: value for key, value in teacher.__dict__.items() if key != '_sa_instance_state'} for teacher in result]
                    else:
                        result = self.session.query(*[getattr(table_class, column) for column in select_columns]).all()
                        self.session.close()
                        return result
                    # result = self.session.query(table_class).all()
                else:
                    if select_columns is None:
                        result = self.session.query(table_class).filter_by(**query_conditions).all()
                        self.session.close()
                        return [{key: value for key, value in teacher.__dict__.items() if key != '_sa_instance_state'}
                                for teacher in result]
                    else:
                        result = self.session.query(
                            *[getattr(table_class, column) for column in select_columns]).filter_by(
                            **query_conditions).all()
                        self.session.close()
                        return result
                    # result = self.session.query(table_class).filter_by(**query_conditions).all()
            except Exception as e:
                raise Exception(e)


        except Exception as e:
            print(f"数据查询过程中发生错误: {str(e)}")
            self.session.rollback()
            self.session.close()
            return None

    @staticmethod
    def convert_to_camel_case_with_underscores(s):
        """
        将下划线分隔的小写字符串转换为每个单词首字母大写的形式

        参数:
        s (str): 输入的字符串，例如 "jinniu_teacher_info"

        返回:
        str: 转换后的字符串，例如 "Jinniu_Teacher_Info"
        """
        # 按下划线分割字符串
        parts = s.split('_')
        # 将每个部分的首字母大写，其余部分保持不变
        capitalized_parts = [part.capitalize() for part in parts]
        # 用下划线重新连接各部分
        return '_'.join(capitalized_parts)
